package org.zjx.db;

import org.zjx.entity.CacheBean;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CacheDB {
    private static boolean isTableCreated = false;  // 标志变量，确保表只创建一次
    private SQLiteHelper dbHelper;

    public CacheDB() {
        dbHelper = SQLiteHelper.getSingleton();
        // 仅在第一次实例化时创建表
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;  // 标志位更新，后续不会再创建表
        }
    }

    // 创建表
    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS cache (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    key TEXT NOT NULL,\n"
                + "    value TEXT NOT NULL,\n"
                + "    create_time INTEGER NOT NULL,\n"
                + "    expire INTEGER NOT NULL\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    // 插入数据
    public void insert(String key, String value, long expire) {
        long createTime = System.currentTimeMillis();

        // Check if the key already exists
        if (findByKey(key) != null) {
            System.out.println("The key already exists in the cache.");
            return; // Skip insert if key already exists
        }

        String sql = "INSERT INTO cache(key, value, create_time, expire) VALUES(?, ?, ?, ?)";
        Object[] params = {key, value, createTime, expire};
        dbHelper.executeUpdate(sql, params);
    }

    // 插入数据
    public void insert(CacheBean cacheBean) {
        // Check if the key already exists
        if (findByKey(cacheBean.getKey()) != null) {
            System.out.println("The key already exists in the cache.");
            return; // Skip insert if key already exists
        }

        String sql = "INSERT INTO cache(key, value, create_time, expire) VALUES(?, ?, ?, ?)";
        Object[] params = {cacheBean.getKey(), cacheBean.getValue(), cacheBean.getCreateTime(), cacheBean.getExpire()};
        dbHelper.executeUpdate(sql, params);
    }

    // 根据key删除数据
    public void deleteByKey(String key) {
        String sql = "DELETE FROM cache WHERE key = ?";
        Object[] params = {key};
        dbHelper.executeUpdate(sql, params);
    }

    // 根据key插入或更新数据
    public void upsert(String key, String value, long expire) {
        long createTime = System.currentTimeMillis();
        CacheBean existingCache = findByKey(key);

        if (existingCache == null) {
            // 如果缓存不存在，插入新数据
            String sql = "INSERT INTO cache(key, value, create_time, expire) VALUES(?, ?, ?, ?)";
            Object[] params = {key, value, createTime, expire};
            dbHelper.executeUpdate(sql, params);
            System.out.println("Inserted new cache entry with key: " + key);
        } else {
            // 如果缓存已存在，更新数据
            String sql = "UPDATE cache SET value = ?, create_time = ?, expire = ? WHERE key = ?";
            Object[] params = {value, createTime, expire, key};
            dbHelper.executeUpdate(sql, params);
            System.out.println("Updated cache entry with key: " + key);
        }
    }

    // 根据CacheBean插入或更新数据
    public void upsert(CacheBean cacheBean) {
        CacheBean existingCache = findByKey(cacheBean.getKey());

        if (existingCache == null) {
            // 如果缓存不存在，插入新数据
            String sql = "INSERT INTO cache(key, value, create_time, expire) VALUES(?, ?, ?, ?)";
            Object[] params = {cacheBean.getKey(), cacheBean.getValue(), cacheBean.getCreateTime(), cacheBean.getExpire()};
            dbHelper.executeUpdate(sql, params);
            System.out.println("Inserted new cache entry with key: " + cacheBean.getKey());
        } else {
            // 如果缓存已存在，更新数据
            String sql = "UPDATE cache SET value = ?, create_time = ?, expire = ? WHERE key = ?";
            Object[] params = {cacheBean.getValue(), cacheBean.getCreateTime(), cacheBean.getExpire(), cacheBean.getKey()};
            dbHelper.executeUpdate(sql, params);
            System.out.println("Updated cache entry with key: " + cacheBean.getKey());
        }
    }

    // 查找数据
    public CacheBean findByKey(String key) {
        String sql = "SELECT * FROM cache WHERE key = ?";
        Object[] params = {key};
        List<CacheBean> cacheBeans = dbHelper.executeQuery(sql, params, CacheBean.class);
        if (cacheBeans.isEmpty()) return null;
        return cacheBeans.get(0);
    }

    // 查找所有数据
    public List<CacheBean> findAll() {
        String sql = "SELECT * FROM cache";
        Object[] params = {};
        return dbHelper.executeQuery(sql, params, CacheBean.class);
    }

    // 分页查询并获取页数
    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        // 计算分页的起始位置 (OFFSET)
        int offset = (pageNum - 1) * pageSize;

        // 查询当前页的数据
        String sql = "SELECT * FROM cache LIMIT ? OFFSET ?";
        Object[] params = {pageSize, offset};
        List<CacheBean> cacheList = dbHelper.executeQuery(sql, params, CacheBean.class);

        // 查询总记录数
        String countSql = "SELECT COUNT(*) FROM cache";
        Object[] countParams = {};
        List<Integer> totalRecordsList = dbHelper.executeQuery(countSql, countParams, Integer.class);
        int totalRecords = 0;
        if (!totalRecordsList.isEmpty()) {
            totalRecords = totalRecordsList.get(0);
        }

        // 计算总页数
        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        // 返回结果
        Map<String, Object> result = new HashMap<>();
        result.put("cacheList", cacheList);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    // 清空数据
    public void clearData() {
        dbHelper.clearData("cache");
    }

    // 删除表
    public void dropTable() {
        dbHelper.dropTable("cache");
    }
}
